<?php

declare(strict_types=1);

namespace App\Model;


use Hyperf\DbConnection\Db;

/**
 * @property int $id
 * @property int $uid 用户主体
 * @property int $type 1收入   2支出
 * @property int $sub_type 收入分为：
 * 1充值收入，2视频通话收入，3收礼收入, 4语音通话收入，5充值赠送， 6红包收入，7接收消息收入 10首充奖励 20完成任务 21签到收入 22注册赠送（仅文字聊天）23聊天金币退回 24体验卡兑换 31收费图片（收） 35推广分成 37查看微信（收） 39查看手机（收） 41查看QQ（收） 43查看私密图片（收） 45查看私密视频（收） 47游戏收入 49砸金蛋（收） 51守护（收） 53 抽奖退还 54 抽奖奖金 61 语聊订单未接单返还金币  62退单返还金币  63购买聊币赠送金币 64邀请用户活动奖励  94语聊房间收礼收入 101秀币兑换金币收入 103查看Twitter 105查看Facebook 107查看whatsApp 108免费通话补贴 109凌晨积分补贴
 * 支出分为：
 * 11视频通话支出，12送礼支出，13提现支出，14语音通话支出, 15发消息支出 25扣除金币 30收费图片（支） 36查看微信（支） 38查看手机（支） 40查看QQ（支） 42查看私密图片（支） 44查看私密视频（支） 46游戏支出 48砸金蛋（支）50守护（支） 52 抽奖支出  60 语聊下单  90房间礼物支出 92房间上麦支出 93房间弹幕支出 100平台补扣 102查看Twitter 104查看Facebook 106查看whatsApp 110兑换金币
 * @property int $amount_type 1金币 2积分
 * @property string $amount 发生金额
 * @property string $account 账户余额
 * @property int $rate 主播收入提成比例（用于公会结算
 * @property int $user_id 对方id，部分type下没有
 * @property string $ext 保存对方用户基本信息json{v:版本,p:包名,c:渠道}
 * @property int $ctime 产生时间
 * @property string $extend 扩展信息
 * @property int $from 1-android 2-IOS
 * @property string $ip
 * @property string $package 包名
 * @property int $version_code 版本号
 * @property string $channel 渠道
 * @property string $guild_num 公会号
 * @property string $gold_from 金币组成部分
 */
class IncomeExpense extends Model
{
    /**
     * The table associated with the model.
     */
    protected ?string $table = 'tb_income_expense';

    /**
     * The attributes that are mass assignable.
     */
    protected array $fillable = [];

    /**
     * The attributes that should be cast to native types.
     */
    protected array $casts = ['id' => 'integer', 'uid' => 'integer', 'type' => 'integer', 'sub_type' => 'integer', 'amount_type' => 'integer', 'rate' => 'integer', 'user_id' => 'integer', 'ctime' => 'integer', 'from' => 'integer', 'version_code' => 'integer'];


    /**
     * Note - 给支出记录，添加收益字段的值
     * DateTime 2024/8/2 10:54
     * User Admin
     * @param $id
     * @param $amount
     * @param $account
     * @param $gold_from
     * @return int
     */
    public static function incrementCallAmount($id, $amount, $account, $gold_from = [])
    {
        $gold_from = json_encode($gold_from);
        // $sql = "UPDATE {$this->table} SET amount = amount+?,account=?,gold_from=? WHERE id=?";
        // $pd = $this->createCommand($sql);
        // $pd->execute([$amount, $account, $gold_from, $id]);
        // return $pd->rowCount();
        return self::query()->where('id', $id)->update(['amount' => Db::raw('amount+' . $amount), 'account' => $account, 'gold_from' => $gold_from]);
    }

    /**
     * 查找用户指定的收支明细
     * @param $id
     * @param $uid
     * @param $type 1收入 2支出
     * @return mixed
     */
    public function GetIncomeInfo($id, $uid, $type)
    {
        // $sql = "select * from {$this->table} where `id` = ? and uid = ? and `type` = ?";
        // try {
        //     $pd = $this->createCommand($sql);
        //     $pd->execute([$id, $uid, $type]);
        //     $rs = $pd->fetch(PDO::FETCH_ASSOC);
        //     return $rs;
        // } catch (PDOException $e) {
        //     Tool::JSON(\CodeData::NO_PROMPT, '-1', 'database error');
        // }
        return self::query()->where('id', $id)->where('uid', $uid)->where('type', $type)->first();
    }


    /**
     * 获取充值记录
     * @param $uid
     * @param $page
     * @param $pagesize
     * @return array
     */
    public function getRechargeList($uid, $page, $pagesize)
    {
        $start = ($page - 1) * $pagesize;
        // $where = "and (sub_type in (1,5) or (sub_type >=50 and sub_type<70)) and sub_type not in (64)";
        // $sql = "select i.id,type,sub_type,amount,account,amount_type,ctime,extend,u.inviteUid from tb_income_expense i left join  tb_user u on u.id = i.user_id where uid = ? and type=? {$where} order by ctime desc,id desc limit {$start},{$pagesize}";
        $data = self::query()->from('tb_income_expense AS i')->leftJoin('tb_user AS u', 'u.id', '=', 'i.user_id')
            ->select(['i.id', 'type', 'sub_type', 'amount', 'account', 'amount_type', 'ctime', 'extend', 'u.inviteUid'])
            ->where('uid', $uid)->where('type', 1)->where('sub_type', '<>', 64)
            ->where(function ($query) {
                $query->whereIn('sub_type', [1, 5])->orWhere(function ($query) {
                    $query->where('sub_type', '>=', 50)->where('sub_type', '<', 70);
                });
            })
            ->orderBy('ctime', 'desc')->orderBy('id', 'desc')->offset($start)->limit($pagesize)->get();
        return $data->toArray();
        // $pd = $this->createCommand($sql);
        // $pd->execute(array($uid, 1));
        // return $pd->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * 获取收益记录
     * @param $uid
     * @param $type 1收益 2支出
     * @param $page
     * @param $pagesize
     * @param int $sub_type 1通话收益 2文字收益 3礼物收益 4其他收益
     * @return array
     */
    public function getList($uid, $type, $page, $pagesize, int $sub_type = 0)
    {
        $start = ($page - 1) * $pagesize;
        $query = self::query()->from('tb_income_expense AS i')->leftJoin('tb_user AS u', 'u.id', '=', 'i.user_id')
            ->select(['i.id', 'type', 'sub_type', 'amount', 'amount_type', 'account', 'ctime', 'extend', 'u.inviteUid', 'i.user_id', 'u.nickname', 'u.head_image', 'i.gold_from'])
            ->where('uid', $uid)->where('type', $type)->where('sub_type', '<>', 100);
        $whereClauses = [];
        if ($type == 1) {
            // $whereClauses[] = "sub_type NOT IN (1, 5)";
            // 充值，充值赠送的不算收益
            $query->whereNotIn('sub_type', [1, 5]);
        }
        /** 1通话收益 2文字收益 3礼物收益 4其他收益 */
        switch ($sub_type) {
            case 1:
                $whereClauses[] = "sub_type IN (2, 4)";
                $query->whereIn('sub_type', [2, 4]);
                break;
            case 2:
                // $whereClauses[] = "sub_type = 7";
                $query->where('sub_type', 7);
                break;
            case 3:
                // $whereClauses[] = "sub_type = 3";
                $query->where('sub_type', 3);
                break;
            case 4:
                // $whereClauses[] = "sub_type NOT IN (2, 3, 4, 7)";
                $query->whereNotIn('sub_type', [2, 3, 4, 7]);
                break;
            default:
                break;
        }

        $whereClauses = implode(' AND ', $whereClauses);
        // $data = self::query()->from('tb_income_expense AS i')->leftJoin('tb_user AS u', 'u.id', '=', 'i.user_id')
        //     ->select(['i.id', 'type', 'sub_type', 'amount', 'amount_type', 'account', 'ctime', 'extend', 'u.inviteUid', 'i.user_id', 'u.nickname', 'u.head_image', 'i.gold_from'])
        //     ->where('uid', $uid)->where('type', $type)->where('sub_type', '<>',100)
        //     ->whereRaw($whereClauses)
        //     ->orderBy('ctime', 'desc')->orderBy('id', 'desc')->offset($start)->limit($pagesize)->get();
        $data = $query->orderBy('ctime', 'desc')->orderBy('id', 'desc')->offset($start)->limit($pagesize)->get();
        return $data->toArray();
        // try {
        // $sql = "select i.id,type,sub_type,amount,amount_type,account,ctime,extend,u.inviteUid,i.user_id,u.nickname,u.head_image,i.gold_from from tb_income_expense i left join  tb_user u on u.id = i.user_id where uid = ? and sub_type!=100 and type=? {$where} order by ctime desc,id desc limit {$start},{$pagesize}";
        // $pd = $this->createCommand($sql);
        // $pd->execute(array($uid, $type));
        // return $pd->fetchAll(PDO::FETCH_ASSOC);
        // } catch (PDOException $e) {
        //     Tool::JSON(\CodeData::NO_PROMPT, '-1', 'database error');
        // }
    }


    /**
     * Note - 推广排行
     * DateTime 2024/8/13 17:34
     * User Admin
     * @param $type
     * @param $page
     * @param $pagesize
     */
    public function getInviteRank($type = 1, $page = 1, $pagesize = 20)
    {
        $start = ($page - 1) * $pagesize;
        $subQuery = self::query()->select('uid', DB::raw('SUM(IFNULL(amount, 0)) as amount'))
            ->where('type', 1)->where('sub_type', 35)->where('amount_type', $type)
            ->groupBy('uid')
            ->orderByDesc('amount')
            ->offset($start)->limit($pagesize);
        return User::query()->from('tb_user as u')
            ->select(['u.id', 'u.nickname', 'u.head_image', 'a.amount'])
            ->rightJoinSub($subQuery, 'a', 'a.uid', '=', 'u.id')
            // ->where('type', 1)->where('sub_type', 35)->where('amount_type', $type)
            ->get()->toArray();

    }


    /**
     * Note - 获取我的推广明细
     * DateTime 2024/8/13 17:42
     * User Admin
     * @param $uid
     * @param $page
     * @param $pagesize
     */
    public function getInviteList($uid, $page = 1, $pagesize = 20)
    {
        // if ($page > 0) {
        //     $start = ($page - 1) * $pagesize;
        //     $limit = ' LIMIT ' . $start . ',' . $pagesize;
        // } else {
        //     $limit = '';
        // }
        // $sql = "select id,ctime,amount,extend from tb_income_expense where sub_type=35 and uid = ? order by ctime DESC" . $limit;
        // $sql = "select u.id,a.ctime,a.amount,a.extend,a.amount_type,u.nickname,u.sex from tb_income_expense a left join tb_user u on a.user_id=u.id where a.sub_type=35 and a.type=1 and a.amount_type=2 and uid = ? order by ctime DESC" . $limit;
        $start = ($page - 1) * $pagesize;
        return self::query()->from('tb_income_expense AS a')->leftJoin('tb_user AS u', 'u.id', '=', 'a.user_id')
            ->select(['a.id', 'a.ctime', 'a.amount', 'a.extend', 'a.amount_type', 'u.nickname', 'u.sex'])
            ->where('a.sub_type', 35)->where('a.type', 1)->where('a.amount_type', 2)->where('uid', $uid)
            ->orderBy('ctime', 'DESC')
            ->offset($start)->limit($pagesize)->get()->toArray();

        // $pd = $this->createCommand($sql);
        // $pd->execute(array($uid));
        // $data = $pd->fetchAll(PDO::FETCH_ASSOC);
        // return $data;

    }


    /**
     * Note - 获取我的推广用户及带来的收益
     * DateTime 2024/8/13 17:46
     * User Admin
     * @param $uid
     * @param $key
     * @return array|void
     */
    public function getInviteUserList($uid, $key)
    {
        $today = strtotime(date('Y-m-d'));
        // $sql = "SELECT id,guild_num,rate FROM tb_guild WHERE uid=$uid LIMIT 1";
        // $rs = $this->findOne($sql);
        $rs = Guild::query()->where('uid', $uid)->select(['id', 'guild_num', 'rate'])->first();
        $rs = modelToArray($rs);

        // $where = empty($key) ? '' : "u.id=$key AND";
        // $sql = <<<SQL
        //     SELECT u.id uid,u.nickname,u.head_image img,u.sex,FROM_UNIXTIME(u.register_time, '%Y-%m-%d') time,IFNULL(a.amount, 0) amount
        //     FROM tb_user u
        //     LEFT JOIN (SELECT user_id,SUM(amount) amount FROM tb_income_expense WHERE type=1 AND sub_type=35 AND amount_type=2 AND uid=$uid GROUP BY user_id) a ON u.id=a.user_id
        //     WHERE $where u.inviteUid = $uid
        // SQL;
        // $data = $this->query($sql);
        $data = User::query()->from('tb_user AS u')
            ->selectRaw("u.id AS uid, u.nickname, u.head_image, u.sex, FROM_UNIXTIME(u.register_time, '%Y-%m-%d') time,IFNULL(a.amount, 0) amount")
            ->leftJoinSub(function ($query) use ($uid) {
                $query->select('user_id', DB::raw('SUM(amount) amount'))
                    ->from('tb_income_expense')
                    ->where('type', 1)->where('sub_type', 35)->where('amount_type', 2)->where('uid', $uid)
                    ->groupBy('user_id');
            }, 'a', 'a.user_id', '=', 'u.id')
            // 推广用户id
            ->where('u.inviteUid', $uid)
            ->when(!empty($key), function ($query) use ($key) {
                $query->where('u.id', $key);
            })
            ->get()->toArray();

        // $sql = "SELECT IFNULL(ROUND(SUM(amount),2),0) amount FROM tb_income_expense WHERE type=1 AND sub_type=35 AND amount_type=2 AND uid=$uid AND ctime>=$today";
        // $todayInviteAward = $this->findOneField($sql, 'amount');
        $todayInviteAward = self::query()->where('type', 1)->where('sub_type', 35)
            ->where('amount_type', 2)->where('uid', $uid)->where('ctime', '>=', $today)
            ->sum(DB::raw('ROUND(IFNULL(amount, 0), 2)'));
        $inviteAward = 0;
        // foreach ($data as $v) {
        //     $inviteAward += $v['amount'];
        // }

        if (empty($rs)) {
            // $sql = "SELECT COUNT(id) count FROM tb_user WHERE inviteUid=$uid";
            // $invitePerson = intval($this->findOneField($sql, 'count'));
            $invitePerson = User::query()->where('inviteUid', $uid)->count();
            // $sql = "SELECT COUNT(id) count FROM tb_user WHERE inviteUid=$uid AND register_time>=$today";
            // $todayInvitePerson = intval($this->findOneField($sql, 'count'));
            $todayInvitePerson = User::query()->where('inviteUid', $uid)->where('register_time', '>=', $today)->count();
        } else {
            $id = $rs['id'];
            $guild_num = $rs['guild_num'];
            $guild_rate = $rs['rate'];
            // $guilds = $this->query("SELECT guild_num FROM tb_guild WHERE parent_id='$id' AND status=1");
            // $guilds = array_column($guilds, "guild_num");
            $guilds = Guild::query()->where('parent_id', $id)->where('status', 1)->pluck('guild_num')->toArray();
            $guilds[] = $guild_num;
            $guilds = implode(",", $guilds);

            // $sql = "SELECT COUNT(id) count FROM tb_user WHERE (inviteUid=$uid OR find_in_set(guild_num,'$guilds'))";
            // $invitePerson = intval($this->findOneField($sql, 'count'));
            $invitePerson = User::query()->where('inviteUid', $uid)->orWhereRaw("find_in_set(guild_num,'$guilds')")->count();

            // $sql = "SELECT COUNT(id) count FROM tb_user WHERE (inviteUid=$uid OR find_in_set(guild_num,'$guilds')) AND register_time>=$today";
            // $todayInvitePerson = intval($this->findOneField($sql, 'count'));
            $todayInvitePerson = User::query()->where('inviteUid', $uid)->orWhereRaw("find_in_set(guild_num,'$guilds')")->where('register_time', '>=', $today)->count();

            // $where = empty($key) ? '' : "u.id=$key AND ";
            // $sql = <<<SQL
            //         SELECT u.id uid,u.nickname,u.head_image img,u.sex,FROM_UNIXTIME(u.register_time, '%Y-%m-%d') time,IFNULL(a.amount, 0) amount
            //         FROM tb_user u
            //         LEFT JOIN (SELECT anchor_id id,SUM(guild_amount + invite_guild_amount) amount FROM tb_guild_bills_detail GROUP BY anchor_id) a ON u.id=a.id
            //         WHERE $where u.guild_num=(SELECT guild_num FROM tb_guild WHERE uid=$uid)
            //     SQL;
            // $guildData = $this->query($sql);
            // 子查询计算每个主播的公会贡献总和
            $subQuery = GuildBillsDetail::query()
                ->select(DB::raw('anchor_id, SUM(guild_amount + invite_guild_amount) as amount'))
                ->groupBy('anchor_id');

            // 主查询
            $guildData = User::query()->from('tb_user AS u')->select([
                'u.id as uid',
                'u.nickname',
                'u.head_image',
                'u.sex',
                DB::raw("FROM_UNIXTIME(u.register_time, '%Y-%m-%d') as time"),
                DB::raw("IFNULL(a.amount, 0) as amount")
            ])->leftJoinSub($subQuery, 'a', function ($join) {
                $join->on('u.id', '=', 'a.anchor_id');
            })->when(!empty($key), function ($query) use ($key) {
                return $query->where('u.id', $key);
            })->whereRaw('u.guild_num = (SELECT guild_num FROM tb_guild WHERE uid = ?)', [$uid])->get()->toArray();

            [$todayData, $ext] = $this->getTodayInviteData($guild_num, $guild_rate, $guilds, $key);
            foreach ($todayData as &$v) {
                unset($v['date'], $v['sex'], $v['income'], $v['guild_amount'], $v['invite_amount'], $v['invite_guild_amount'], $v['second_guild']);
            }

            $this->mergeData($guildData, $data, 'uid', ['amount']);
            $this->mergeData($todayData, $data, 'uid', ['amount']);

            $todayInviteAward += $ext['amount'];
            foreach ($data as &$v) {
                $inviteAward += $v['amount'];
                $v['amount'] = round(floatval($v['amount']), 2);
            }
        }

        $inviteAward = round($inviteAward, 2);
        $todayInviteAward = round($todayInviteAward, 2);
        $sort = array_column($data, 'amount');
        array_multisort($sort, SORT_DESC, $data);
        $ext = compact('invitePerson', 'inviteAward', 'todayInvitePerson', 'todayInviteAward');
        return [$data, $ext];
    }


    /**
     * Note - 收益明细-按天统计
     * DateTime 2024/8/13 18:13
     * User Admin
     * @param $uid
     * @return array|void
     */
    public function getIncomeByDay($uid)
    {
        $date = date('Y-m-d');
        $time = strtotime($date) - 86400 * 14;
        // $sql = "SELECT id,guild_num,rate FROM tb_guild WHERE uid=$uid LIMIT 1";
        // $rs = $this->findOne($sql);
        $rs = Guild::query()->where('uid', $uid)->select(['id', 'guild_num', 'rate'])->first();
        $rs = modelToArray($rs);
        if (empty($rs)) {
            // $sql = "SELECT DATE_FORMAT(FROM_UNIXTIME(ctime), '%Y-%m-%d') date,COUNT(DISTINCT user_id) count,ROUND(IFNULL(SUM(amount),0)/10,2) amount FROM tb_income_expense WHERE uid=$uid AND type=1 AND sub_type=35 AND amount_type=2 AND ctime>=$time GROUP BY date";
            // $data = $this->query($sql);
            $data = self::query()
                ->selectRaw('DATE_FORMAT(FROM_UNIXTIME(ctime), "%Y-%m-%d") as date, COUNT(DISTINCT user_id) as count, ROUND(IFNULL(SUM(amount),0)/10,2) as amount')
                ->where('uid', $uid)
                ->where('type', 1)
                ->where('sub_type', 35)
                ->where('amount_type', 2)
                ->where('ctime', '>=', $time)
                ->groupBy('date')->get()->toArray();
        } else {
            $guild_id = $rs['id'];
            $guild_rate = $rs['rate'];
            $guild_num = $rs['guild_num'];
            // $guilds = $this->query("SELECT guild_num FROM tb_guild WHERE parent_id='$guild_id' AND status=1");
            // $guilds = array_column($guilds, "guild_num");
            $guilds = Guild::query()->where('parent_id', $guild_id)->where('status', 1)->pluck('guild_num')->toArray();
            $guilds[] = $guild_num;
            $guilds = implode(",", $guilds);

            // $sql = <<<SQL
            //         SELECT DATE_FORMAT(FROM_UNIXTIME(s_time), '%Y-%m-%d') date,COUNT(DISTINCT anchor_id) count,SUM(guild_amount + invite_guild_amount) amount
            //         FROM tb_guild_bills_detail
            //         WHERE guild_num=(SELECT guild_num FROM tb_guild WHERE uid=$uid) AND s_time>=$time
            //         GROUP BY date
            //     SQL;
            // $data = $this->query($sql);
            $data = GuildBillsDetail::query()->select([
                DB::raw("DATE_FORMAT(FROM_UNIXTIME(s_time), '%Y-%m-%d') date"),
                DB::raw("COUNT(DISTINCT anchor_id) count"),
                DB::raw("SUM(guild_amount + invite_guild_amount) amount")
            ])->where('s_time', '>=', $time)
                ->whereRaw('guild_num = (SELECT guild_num FROM tb_guild WHERE uid = ?)', [$uid])
                ->groupBy('date')
                ->orderBy('date')->get()->toArray();

            [$todayData, $ext] = $this->getTodayInviteData($guild_num, $guild_rate, $guilds);
            $todayData = [['date' => $date, 'count' => count($todayData), 'amount' => $ext['amount']]];

            $this->mergeData($todayData, $data, 'date', ['amount'], true);
            foreach ($data as &$v) {
                $v['amount'] = bcdiv(strval($v['amount']), "10", 2);
            }
        }
        array_multisort(array_column($data, 'date'), SORT_DESC, $data);
        $amount = round(array_sum(array_column($data, 'amount')), 2);
        return [$data, $amount];

    }


    /**
     * Note - 收益明细-某天详情
     * DateTime 2024/8/13 18:22
     * User Admin
     * @param $uid
     * @param $date
     * @return array|void
     */
    public function getIncomeDetail($uid, $date)
    {
        $today = date('Y-m-d');
        $startTime = strtotime($date);
        $endTime = $startTime + 86400;
        // $sql = "SELECT id,guild_num,rate FROM tb_guild WHERE uid=$uid LIMIT 1";
        // $rs = $this->findOne($sql);
        $rs = Guild::query()->where('uid', $uid)->select(['id', 'guild_num', 'rate'])->first();
        $rs = modelToArray($rs);
        if (empty($rs)) {
            $flag = 0;
            // $sql = <<<SQL
            //         SELECT '$date' date,e.user_id uid,u.nickname,FROM_UNIXTIME(u.register_time) time,ROUND(IFNULL(SUM(e.amount),0) / 10,2) amount FROM tb_income_expense e
            //         LEFT JOIN tb_user u ON u.id=e.user_id
            //         WHERE uid=$uid AND type=1 AND sub_type=35 AND amount_type=2 AND ctime>=$startTime AND ctime<$endTime GROUP BY user_id
            //     SQL;
            // $data = $this->query($sql);
            $data = self::query()->from('tb_income_expense AS e')->select([
                DB::raw("'$date' date"),
                DB::raw("e.user_id uid"),
                DB::raw("u.nickname"),
                DB::raw("FROM_UNIXTIME(u.register_time) time"),
                DB::raw("ROUND(IFNULL(SUM(e.amount),0) / 10,2) amount")
            ])->join('tb_user AS u', 'u.id', '=', 'e.user_id')
                ->where('uid', $uid)
                ->where('type', 1)
                ->where('sub_type', 35)
                ->where('amount_type', 2)
                ->where('e.ctime', '>=', $startTime)
                ->where('e.ctime', '<', $endTime)
                ->groupBy('user_id')->get()->toArray();
        } else {
            $flag = 1;
            // $sql = <<<SQL
            //         SELECT '$date' date,e.user_id uid,u.nickname,FROM_UNIXTIME(u.register_time) time,0 income,0 guild_amount,0 invite_amount,0 invite_guild_amount,SUM(e.amount) amount,'' second_guild,json_extract(u.ext2,'$.bind_time') bind_time
            //         FROM tb_income_expense e
            //         LEFT JOIN tb_user u ON u.id=e.user_id
            //         WHERE uid=$uid AND type=1 AND sub_type=35 AND amount_type=2 AND ctime>=$startTime AND ctime<$endTime GROUP BY user_id
            //     SQL;
            // $data = $this->query($sql);
            $data = self::query()->from('tb_income_expense AS e')->select([
                DB::raw("'$date' date"),
                DB::raw("e.user_id uid"),
                DB::raw("u.nickname"),
                DB::raw("FROM_UNIXTIME(u.register_time) time"),
                DB::raw("0 income"),
                DB::raw("0 guild_amount"),
                DB::raw("0 invite_amount"),
                DB::raw("0 invite_guild_amount"),
                DB::raw("SUM(e.amount) amount"),
                DB::raw("'' second_guild"),
                DB::raw("json_extract(u.ext2,'$.bind_time') bind_time")
            ])->join('tb_user AS u', 'u.id', '=', 'e.user_id')
                ->where('uid', $uid)
                ->where('type', 1)
                ->where('sub_type', 35)
                ->where('amount_type', 2)
                ->where('ctime', '>=', $startTime)
                ->where('ctime', '<', $endTime)
                ->groupBy('user_id')->get()->toArray();
            if ($date == $today) {
                $guild_id = $rs['id'];
                $guild_rate = $rs['rate'];
                $guild_num = $rs['guild_num'];
                // $guilds = $this->query("SELECT guild_num FROM tb_guild WHERE parent_id='$guild_id' AND status=1");
                // $guilds = array_column($guilds, "guild_num");
                $guilds = Guild::query()->where('parent_id', $guild_id)->where('status', 1)->pluck('guild_num')->toArray();

                $guilds[] = $guild_num;
                $guilds = implode(",", $guilds);
                [$todayData, $ext] = $this->getTodayInviteData($guild_num, $guild_rate, $guilds);
                foreach ($todayData as &$v) {
                    unset($v['sex'], $v['img']);
                }
                if (empty($data)) {
                    $data = $todayData;
                } else {
                    $this->mergeData($todayData, $data, 'uid', ['income', 'guild_amount', 'invite_amount', 'invite_guild_amount', 'amount']);
                }
            } else {
                // $sql = <<<SQL
                //         SELECT '$date' date,d.anchor_id uid,u.nickname,FROM_UNIXTIME(u.register_time) time,
                //         SUM(d.amount) income,SUM(d.guild_amount) guild_amount,SUM(d.invite_amount) invite_amount,SUM(d.invite_guild_amount) invite_guild_amount,0 amount,d.second_guild,json_extract(u.ext2,'$.bind_time') bind_time
                //         FROM tb_guild_bills_detail d
                //         LEFT JOIN tb_user u ON u.id=d.anchor_id
                //         WHERE d.guild_num=(SELECT guild_num FROM tb_guild WHERE uid=$uid) AND d.s_time=$startTime GROUP BY d.anchor_id
                //     SQL;
                // $guildData = $this->query($sql);
                $guildData = GuildBillsDetail::query()->from('tb_guild_bills_detail AS d')->select([
                    DB::raw("'$date' date"),
                    DB::raw("d.anchor_id uid"),
                    DB::raw("u.nickname"),
                    DB::raw("FROM_UNIXTIME(u.register_time) time"),
                    DB::raw("SUM(d.amount) income"),
                    DB::raw("SUM(d.guild_amount) guild_amount"),
                    DB::raw("SUM(d.invite_amount) invite_amount"),
                    DB::raw("SUM(d.invite_guild_amount) invite_guild_amount"),
                    DB::raw("0 amount"),
                    DB::raw("d.second_guild"),
                    DB::raw("json_extract(u.ext2,'$.bind_time') bind_time")
                ])->leftJoin('tb_user AS u', 'u.id', '=', 'd.anchor_id')
                    // ->where('d.guild_num', function ($query) use ($uid) {
                    //     $query->from('tb_guild')->select('guild_num')->where('uid', $uid)->limit(1);
                    // })
                    ->whereRaw('d.guild_num = (SELECT guild_num FROM tb_guild WHERE uid = ?)', [$uid])
                    ->where('d.s_time', $startTime)
                    ->groupBy('d.anchor_id')->get()->toArray();
                if (empty($data)) {
                    $data = $guildData;
                } else {
                    $this->mergeData($guildData, $data, 'uid', ['income', 'guild_amount', 'invite_amount', 'invite_guild_amount', 'amount']);
                }
            }

            foreach ($data as &$v) {
                $v['income'] = bcdiv(strval($v['income']), '10', 2);
                $v['guild_amount'] = bcdiv(strval($v['guild_amount']), '10', 2);
                $v['invite_amount'] = bcdiv(strval($v['invite_amount']), '10', 2);
                $v['invite_guild_amount'] = bcdiv(strval($v['invite_guild_amount']), '10', 2);
                $v['amount'] = bcadd($v['guild_amount'], $v['invite_guild_amount'], 2);
            }
        }

        $sort = array_column($data, 'amount');
        array_multisort($sort, SORT_DESC, $data);

        $amount = round(array_sum($sort), 2);
        $header = empty($flag) ? '日期,ID,姓名,注册时间,收益' : '日期,ID,姓名,注册时间,女用户收入,代理提成金额,女用户邀请奖励,邀请奖励提成,收益,下级代理,绑定时间';

        if ($flag) {
            // $bindTimes = $this->query("select uid,min(ctime) ctime from tb_income_expense where guild_num = '{$rs['guild_num']}' group by uid");
            $bindTimes = self::query()->selectRaw('uid, min(ctime) ctime')->where('guild_num', $rs['guild_num'])->groupBy('uid')->get()->toArray();
            if (!empty($bindTimes)) {
                $bindTimes = array_column($bindTimes, 'ctime', 'uid');
            }
            foreach ($data as &$vv) {
                $vv['bind_time'] = date("Y-m-d H:i:s", $vv['bind_time'] ?: ($bindTimes[$vv['uid']] ?? strtotime($vv['time'])));
            }
        }

        return [$amount, $header, $data, $flag];

    }


    public function getTodayInviteData($guild_num, $guild_rate, $guilds, $key = null)
    {
        $date = date('Y-m-d');
        $today = strtotime($date);

        // $where = empty($key) ? '' : " u.id=$key AND ";
        // $sql = <<<SQL
        //     SELECT '$date' date,a.uid,u.nickname,u.head_image img,u.sex,FROM_UNIXTIME(u.register_time, '%Y-%m-%d') time,a.guild_num,a.rate,g.rate g_rate,
        //     SUM(IF(sub_type=2 OR sub_type=3 OR sub_type=4 OR sub_type=7,amount,0)) income,0 guild_amount,SUM(IF(sub_type=35,amount,0)) invite_amount,json_extract(u.ext2,'$.bind_time') bind_time
        //     FROM tb_income_expense a
        //     LEFT JOIN tb_user u on u.id=a.uid
        //     LEFT JOIN tb_guild g ON a.guild_num = g.guild_num
        //     WHERE $where u.status=0 AND a.sub_type in (2,3,4,7,35) AND a.type=1 AND amount_type=2 AND a.ctime>=$today AND find_in_set(a.guild_num,'$guilds')
        //     GROUP BY a.uid
        // SQL;
        // $data = $this->query($sql);
        $data = IncomeExpense::query()->from('tb_income_expense AS a')->selectRaw("'$date' date,
        a.uid,
        u.nickname,
        u.head_image,
        u.sex,
        FROM_UNIXTIME(u.register_time, '%Y-%m-%d') time,
        a.guild_num,
        a.rate,
        g.rate g_rate,
        SUM(IF(sub_type=2 OR sub_type=3 OR sub_type=4 OR sub_type=7,amount,0)) income,
        0 guild_amount,
        SUM(IF(sub_type=35,amount,0)) invite_amount,
        json_extract(u.ext2,'$.bind_time') bind_time")
            ->leftJoin('tb_user AS u', 'u.id', '=', 'a.uid')
            ->leftJoin('tb_guild AS g', 'a.guild_num', '=', 'g.guild_num')
            ->where('u.status', 0)
            ->whereIn('a.sub_type', [2, 3, 4, 7, 35])
            ->where('a.type', 1)
            ->where('amount_type', 2)
            ->where('a.ctime', '>=', $today)
            ->whereRaw("find_in_set(a.guild_num,'$guilds')")
            ->when(!empty($key), function ($query) use ($key) {
                return $query->where('uid', $key);
            })
            ->groupBy('a.uid')
            ->get()->toArray();
        $amount = $guild_amount = $invite_guild_amount = 0;
        foreach ($data as &$li) {
            $bind_time = $li['bind_time'];
            unset($li['bind_time']);
            if ($li['income'] > 0) {
                $li_guild_amount = ($li['guild_num'] == $guild_num) ? ($li['income'] * ($guild_rate - $li['rate']) / $li['rate']) : ($li['income'] * ($guild_rate - $li['g_rate']) / $li['rate']);
            } else {
                $li_guild_amount = 0;
            }
            if ($li['invite_amount'] > 0) {
                $li_invite_guild_amount = ($li['guild_num'] == $guild_num) ? $li['invite_amount'] * 0.5 : ($li['invite_amount'] * ($guild_rate - $li['g_rate']) / $li['rate']);
            } else {
                $li_invite_guild_amount = 0;
            }

            $guild_amount += $li_guild_amount;
            $invite_guild_amount += $li_invite_guild_amount;
            $amount += ($li_guild_amount + $li_invite_guild_amount);

            $li['guild_amount'] = round($li_guild_amount, 2);
            $li['invite_guild_amount'] = round($li_invite_guild_amount, 2);
            $li['amount'] = round($li_guild_amount + $li_invite_guild_amount, 2);
            $li['second_guild'] = ($guild_num == $li['guild_num']) ? '' : $li['guild_num'];
            $li['bind_time'] = $bind_time;
            unset($li['guild_num'], $li['rate'], $li['g_rate']);
        }

        $ext = compact('amount', 'guild_amount', 'invite_guild_amount');
        return [$data, $ext];
    }


    private function mergeData($src, &$dst, $compare, $fields, $precision = false)
    {
        foreach ($src as $s) {
            $found = false;
            foreach ($dst as &$d) {
                if ($s[$compare] === $d[$compare]) {
                    foreach ($fields as $field) {
                        $d[$field] = $precision ? round($d[$field] + $s[$field], 2) : ($d[$field] + $s[$field]);
                    }
                    $found = true;
                    break;
                }
            }

            if (!$found) {
                $dst[] = $s;
            }
        }
    }


}
